# -*- coding:utf-8 -*-
import os
import pymysql
import json

with open('tp_source_gallery.json', 'rb') as f:
    json_data = f.read()
json_data = json.loads(json_data)

mysql_config = {
    'host': '127.0.0.1',  # ip地址(本机地址可以写 localhost 或 127.0.0.1)
    'port': 3306,  # 端口号
    'user': 'root',  # 用户名
    'password': 'root',  # 密码
    'database': 'xt',  # 数据库名
    'charset': 'utf8',  # 字符集
}

for item in json_data:
    connect = pymysql.connect(**mysql_config)
    cursor = connect.cursor()  # 创建游标
    cat = item['category']
    try:
        sql = 'select id from cat where name=' + '"' + cat + '"'+'and '+'source=2'
        cursor.execute(sql)
    except Exception as e:
        print(e, 'select cat id')
        cursor.close()
        connect.close()
    else:
        cat_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖
        # category查询到对应的catid
        if cat_tuple:
            cat_id = cat_tuple[0]
            brand = item['brand_id']
            # 先查询brand表是否有这个数据
            try:
                sql = 'select id from brand where cat_id=' + str(cat_id)+' and '+'name='+'"'+brand+'"'
                cursor.execute(sql)
            except Exception as e:
                print(e, 'select brand is exists')
                cursor.close()
                connect.close()
            else:
                brand_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖
                # 没有查询到有这条数据则执行插入
                if not brand_tuple:
                    try:
                        # 插入语句
                        sql = 'insert into brand (name,cat_id) values (' + '"' + brand + '"' + ',' + str(
                            cat_id) + ')'
                        # 执行插入语句
                        result = cursor.execute(sql)
                    except Exception as e:
                        print(e, 'insert brand')
                        cursor.close()
                        connect.close()
                    else:
                        brand_id = cursor.lastrowid
                if brand_tuple:
                    brand_id = brand_tuple[0]

                model = item['model_name']
                goods_name = item['goods_name']
                try:
                    sql = 'select id from goods where model_name=' + '"' + model + '"' + ' and goods_name=' + '\'' + goods_name + '\''
                    cursor.execute(sql)
                except Exception as e:
                    print(e, 'select goods is exists')
                    cursor.close()
                    connect.close()
                else:
                    goods_tuple = cursor.fetchone()  # 返回符合查询条件的第一个数据,返回元祖
                    url = item['url']
                    if 'market_price' and 'price_tag' in item:
                        market_price = str(item['market_price'])

                        price_tag = str(item['price_tag'])

                        original_img = item['goods_gallery']
                        image = item['goods_content']
                        attr_value = item['attr_value']
                        goods_color = item['goods_color']
                        goods_attr = item['goods_attr']
                        if not goods_tuple:
                            try:
                                # 插入语句
                                sql = 'insert into goods (cat_id,model_name,goods_name,url,original_img,image,attr_value,price,brand_id,goods_color,goods_attr,market_price) values (' +str(cat_id)+',' + '"'+model+'"'+ ','+ '\'' + goods_name + '\''+','+'"'+url+'"'+','+'"'+original_img+'"'+','+'"'+image+'"'+','+'\''+attr_value+'\''+','+'"'+price_tag+'"'+','+str(brand_id)+','+'"'+goods_color+'"'+','+'"'+goods_attr+'"'+','+'"'+market_price+'"'+')'
                                # 执行插入语句
                                result = cursor.execute(sql)
                            except Exception as e:
                                print(e, 'insert goods',attr_value,image)
                                cursor.close()
                                connect.close()
                            else:
                                connect.commit()

# 关闭游标和连接对象，释放资源
# cursor.close()
# connect.close()
print('操作完成')
